package com.css.fxfzypg.modules.baseData.repository.impl;


import com.alibaba.fastjson.JSONObject;
import com.css.fxfzypg.constants.FxfzConstants;
import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.queryParams.GdpQueryParams;
import com.css.fxfzypg.modules.baseData.repository.GdpRepositpory;
import com.css.fxfzypg.modules.baseData.entity.GdpEntity;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @create 2022-08-11 16:54
 */
@Repository
public class GdpRepositoryImpl implements GdpRepositpory {


    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    SysRoleService sysRoleService;

    @Autowired
    SUserService sUserService;

    @Override
    public int saveGdp(GdpEntity gdpEntity) {
        String sql = "insert into data_gdp values(:gdpId, :province, :city, :prefecture, :gdp, :peoAvgGdp, :isDelete, " +
                ":createTime,:updateTime,:provinceId,:cityId,:prefectureId,:createUser,:updateUser,:provinceCode, :cityCode, :prefectureCode) ";
        SqlParameterSource source = new BeanPropertySqlParameterSource(gdpEntity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public GdpEntity getGdpById(String id) {
        String sql = "select * from data_gdp where gdp_id='" + id + "' and is_delete='" + YNEnum.N.toString() + "'";
        GdpEntity gdpEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(GdpEntity.class));
        return gdpEntity;
    }

    @Override
    public GdpEntity getGdpByDivision(String province, String city, String prefecture) {
        String sql = "select * from data_gdp where province='" + province + "' and city='" + city + "' and " +
                "prefecture='" + prefecture + "' " + " and is_delete='" + YNEnum.N.toString() + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(GdpEntity.class));
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    public int updateGdp(GdpEntity gdpDTO) {
        String sql = "update data_gdp set gdp = '" + gdpDTO.getGdp() + "', peo_avg_gdp = '" + gdpDTO.getPeoAvgGdp() +
                "',update_user='" + gdpDTO.getUpdateUser() + "',update_time='" + gdpDTO.getUpdateTime() + "'  " +
                "where gdp_id='" + gdpDTO.getGdpId() + "' and is_delete='" + YNEnum.N.toString() + "'";
        return jdbcTemplate.update(sql);
    }


    @Override
    public JSONObject getAllPeoByParams(GdpQueryParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder(" select  u.* from data_gdp u ");
        StringBuilder whereSql = new StringBuilder(" where u.is_delete = '" + YNEnum.N.toString() + "'");

        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for (Map roleMap : roleMaps) {
            str.append(roleMap.get("roleCode") + ",");
        }
        String roles = str.toString();
        if (roles.contains(FxfzConstants.YPG_PRI_YW_ROLE) || roles.contains(FxfzConstants.YPG_PRI_GL_ROLE) && !roles.contains(FxfzConstants.YPG_COUN_GL_ROLE) && !roles.contains(FxfzConstants.YPG_COUN_YW_ROLE)) {
            whereSql.append(" and u.province_id = '" + sUser.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and u.province_id ='" + queryParams.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and u.city_id  ='" + queryParams.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getPrefectureId())) {
            whereSql.append(" and u.prefecture_id ='" + queryParams.getPrefectureId() + "'");
        }
//        StringBuilder orderSql = new StringBuilder(" order by u.prefecture_code");
        StringBuilder orderSql = new StringBuilder();
        if ("0".equals(queryParams.getOption())) {
            orderSql.append(" order by u.province_code,u.city_code,u.prefecture_code asc");
        }
        if ("1".equals(queryParams.getOption())) {
            orderSql.append(" order by COALESCE (u.update_time,u.create_time) desc");
        }
        if ("2".equals(queryParams.getOption())) {
            if ("0".equals(queryParams.getOption1())) {
                orderSql.append("order by gdp asc");
            }
            if ("1".equals(queryParams.getOption1())) {
                orderSql.append("order by gdp desc nulls last");
            }
        }
        if ("3".equals(queryParams.getOption())) {
            if ("0".equals(queryParams.getOption1())) {
                orderSql.append("order by peo_avg_gdp asc");
            }
            if ("1".equals(queryParams.getOption1())) {
                orderSql.append("order by peo_avg_gdp desc nulls last");
            }
        }
        sql.append(whereSql).append(orderSql).toString();
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = curPage * pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(GdpEntity.class)).size();
        List<GdpEntity> gdpEntities = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(GdpEntity.class));
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("rows", gdpEntities);
        jsonObject.put("total", total);
        return jsonObject;
    }

    @Override
    public void deleteGdpByIds(String id, String userId, Timestamp currentTimestamp) {
        String sql = "update data_gdp set is_delete = '" + YNEnum.Y.toString() + "' ," +
                " update_time = '" + currentTimestamp + "' ," + " update_user = '" + userId + "' " +
                " where gdp_id = '" + id + "' and is_delete='" + YNEnum.N.toString() + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public int[] importExcel(List<GdpEntity> gdpEntities) {
        String sql = "update data_gdp set gdp = ?, peo_avg_gdp = ?, update_user=?, update_time= ?" +
                " where province=? and city=? and prefecture=?";

        int[] ints = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setBigDecimal(1, gdpEntities.get(i).getGdp());
                ps.setBigDecimal(2, gdpEntities.get(i).getPeoAvgGdp());
                ps.setString(3, gdpEntities.get(i).getUpdateUser());
                Date sqlDate = new Date(gdpEntities.get(i).getUpdateTime().getTime());
                ps.setDate(4, sqlDate);
                ps.setString(5, gdpEntities.get(i).getProvince());
                ps.setString(6, gdpEntities.get(i).getCity());
                ps.setString(7, gdpEntities.get(i).getPrefecture());
            }

            @Override
            public int getBatchSize() {
                return gdpEntities.size();
            }
        });
        return ints;
    }

    @Override
    public List<GdpEntity> exportExcel() {
        return jdbcTemplate.query("select u.* from data_gdp  u where u.is_delete='" + YNEnum.N.toString() + "'",
                new BeanPropertyRowMapper<>(GdpEntity.class));
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_gdp where prefecture = '" + county + "' and city = '" + city + "' and province = '" + province + "' and is_delete ='0' and ( gdp is not null or peo_avg_gdp is not null)   ", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_gdp where city = '" + city + "' and province = '" + province + "' and is_delete ='0' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String name) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_gdp where province = '" + name + "' and is_delete ='0'", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_gdp where province = ? and (TO_CHAR(update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and is_delete ='0' ", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }


}
